﻿using MSharp.Data.SPI;
using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MSharp.Data.DatabaseInfo
{
    public class SqlServerDBInfo : IDBInfo
    {

        private DB Db { get; set; }

        public SqlServerDBInfo(DB db)            
        {
            this.Db = db;
            Refresh();
        }

        public string DBName
        {
            get { return (Db.ConnectionStringBuilder as System.Data.SqlClient.SqlConnectionStringBuilder).InitialCatalog; }
        }

        public NameValueCollection TableComments { get; private set; }
        public List<string> TableNames { get; private set; }        
        public Dictionary<string, TableInfo> TableInfoDict { get; private set; }
        public Dictionary<string, List<string>> TableColumnNameDict { get; private set; }
        public Dictionary<string, List<ColumnInfo>> TableColumnInfoDict { get; private set; }

        public Dictionary<string, NameValueCollection> TableColumnComments { get; private set; }

        private Dictionary<string, ColumnInfo> DictColumnInfo { get; set; }

        public List<string> DBNames { get; set; }

        public ColumnInfo this[string tableName, string columnName]
        {
            get
            {
                ColumnInfo colInfo;
                var strKey = (tableName + "@" + columnName).ToLower();
                DictColumnInfo.TryGetValue(strKey, out colInfo);
                return colInfo;
            }
        }

        public List<string> this[string tableName]
        {
            get
            {
                List<string> colNames;
                TableColumnNameDict.TryGetValue(tableName, out colNames);
                return colNames;
            }
        }

        public bool Refresh()
        {
            try
            {
                string dbSql = "select name from sysdatabases Order By name asc";
                DBNames = Db.ReadList<string>(dbSql);

                string strSql = "SELECT a.Name,(SELECT TOP 1 Value FROM sys.extended_properties b WHERE b.major_id=a.id and b.minor_id=0) AS value FROM sysobjects a WHERE a.xtype = 'U' AND a.name <> 'sysdiagrams' AND a.name <> 'dtproperties' ORDER BY a.name asc";
                this.TableComments = Db.ReadNameValues(strSql);

                if (TableComments != null && TableComments.Count > 0)
                {
                    this.TableNames = TableComments.AllKeys.ToList();
                    
                    this.TableInfoDict = new Dictionary<string, TableInfo>(StringComparer.OrdinalIgnoreCase);
                    this.TableColumnNameDict = new Dictionary<string, List<string>>(StringComparer.OrdinalIgnoreCase);
                    this.TableColumnInfoDict = new Dictionary<string, List<ColumnInfo>>(StringComparer.OrdinalIgnoreCase);
                    this.TableColumnComments = new Dictionary<string, NameValueCollection>(StringComparer.OrdinalIgnoreCase);

                    this.DictColumnInfo = new Dictionary<string, ColumnInfo>();
                    foreach (var tableName in TableNames)
                    {
                        TableInfo tabInfo = new TableInfo();
                        tabInfo.TableName = tableName;
                        tabInfo.TabComment = TableComments[tableName];

                        strSql = @"SELECT a.colorder Colorder,a.name ColumnName,b.name TypeName,(case when (SELECT count(*) FROM sysobjects  WHERE (name in (SELECT name FROM sysindexes  WHERE (id = a.id) AND (indid in  (SELECT indid FROM sysindexkeys  WHERE (id = a.id) AND (colid in  (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  AND (xtype = 'PK'))>0 then 1 else 0 end) IsPK,(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end) IsIdentity,  CASE When b.name ='uniqueidentifier' Then 36  WHEN (charindex('int',b.name)>0) OR (charindex('time',b.name)>0) THEN NULL ELSE  COLUMNPROPERTY(a.id,a.name,'PRECISION') end as [Length], CASE WHEN ((charindex('int',b.name)>0) OR (charindex('time',b.name)>0)) THEN NULL ELSE isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),null) end as Scale,(case when a.isnullable=1 then 1 else 0 end) CanNull,Replace(Replace(IsNull(e.text,''),'(',''),')','') DefaultVal,isnull(g.[value], ' ') AS DeText FROM  syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id left join sys.extended_properties f on d.id=f.class and f.minor_id=0 where b.name is not NULL and d.name=@tableName order by a.id,a.colorder";

                        tabInfo.Colnumns = Db.QueryTable(strSql, new { tableName = tableName }).ConvertToListObject<ColumnInfo>();


                        List<string> lstColName = new List<string>();
                        NameValueCollection nvcColDeText = new NameValueCollection();
                        foreach (ColumnInfo colInfo in tabInfo.Colnumns)
                        {
                            lstColName.Add(colInfo.ColumnName);
                            nvcColDeText.Add(colInfo.ColumnName, colInfo.DeText);

                            var strKey = (tableName + "@" + colInfo.ColumnName).ToLower();
                            DictColumnInfo.Add(strKey, colInfo);

                            if (colInfo.IsPK)
                            {
                                tabInfo.PriKeyColName = colInfo.ColumnName;
                                if (colInfo.IsIdentity)
                                {
                                    tabInfo.PriKeyType = PrimaryKeyType.AUTO;
                                }
                                else
                                {
                                    tabInfo.PriKeyType = PrimaryKeyType.SET;
                                }
                            }
                        }

                        TableInfoDict.Add(tableName, tabInfo);
                        TableColumnNameDict.Add(tableName, lstColName);
                        TableColumnInfoDict.Add(tableName, tabInfo.Colnumns);
                        TableColumnComments.Add(tableName, nvcColDeText);
                    }
                }
            }
            catch(Exception ex)
            {
                return false;
            }
            return true;
        }


        public bool IsExistTable(string tableName)
        {
            return TableNames.Contains(tableName, StringComparer.OrdinalIgnoreCase);
        }

        public bool IsExistColumn(string tableName, string columnName)
        {
            var strKey = (tableName + "@" + columnName).ToLower();
            return DictColumnInfo.ContainsKey(strKey);
        }

        public Dictionary<string, DateTime> GetTableStruct_Modify()
        {
            string strSql = "select name,modify_date from sys.objects where type='U' and name <> 'dtproperties' and name <>'sysdiagrams'  order by modify_date desc";
            return Db.ReadDictionary<string, DateTime>(strSql);
        }


        public string GetColumnComment(string tableName, string columnName)
        {
            ColumnInfo colInfo = null;
            var strKey = (tableName + "@" + columnName).ToLower();
            DictColumnInfo.TryGetValue(strKey, out colInfo);
            return colInfo?.DeText;
        }


        public string GetTableComment(string tableName)
        {
            return TableComments[tableName];
        }

        public List<ColumnInfo> GetColumns(string tableName)
        {
            List<ColumnInfo> colInfos = null;
            TableColumnInfoDict.TryGetValue(tableName, out colInfos);
            return colInfos;
        }


        public bool SetTableComment(string tableName, string comment)
        {
            if (!TableNames.Contains(tableName, StringComparer.OrdinalIgnoreCase))
            {
                //throw new ArgumentException("该表不存在！" + tableName, "tableName");
                return false;
            }

            string upsert_sql = string.Empty;
            comment = (comment ?? string.Empty).Replace("'", "");           
            try
            {
                upsert_sql = @" if exists (
                 SELECT case when a.colorder = 1 then d.name  else '' end as 表名,  case when a.colorder = 1 then isnull(f.value, '')  else '' end as 表说明
                FROM syscolumns a 
                       inner join sysobjects d 
                          on a.id = d.id 
                             and d.xtype = 'U' 
                             and d.name <> 'sys.extended_properties'
                       left join sys.extended_properties   f 
                         on a.id = f.major_id 
                            and f.minor_id = 0
                 where a.colorder = 1 and d.name<>'sysdiagrams'  and d.name='{0}' and f.value is not null
                 )
                 exec sp_updateextendedproperty N'MS_Description', N'{1}', N'user', N'dbo', N'table', N'{0}', NULL, NULL
                 else
                exec sp_addextendedproperty N'MS_Description', N'{1}', N'user', N'dbo', N'table', N'{0}', NULL, NULL";
                upsert_sql = string.Format(upsert_sql, tableName, comment);
                Db.ExecSql(upsert_sql);

                TableComments[tableName] = comment;
                
                var tabInfo = TableInfoDict[tableName];
                tabInfo.TabComment = comment;
                TableInfoDict[tableName] = tabInfo;
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }


        public bool SetColumnComment(string tableName, string columnName, string comment)
        {
            if (!TableNames.Contains(tableName, StringComparer.OrdinalIgnoreCase))
            {
                //throw new ArgumentException("该表不存在！" + tableName, "tableName");
                return false;
            }

            var strKey = (tableName + "@" + columnName).ToLower();
            if (!DictColumnInfo.ContainsKey(strKey))
            {
                //throw new ArgumentException(tableName + "表不存在" + columnName + "列！", "columnName");
                return false;
            }

            string upsert_sql = string.Empty;
            comment = (comment ?? string.Empty).Replace("'", "");
            try
            {
                upsert_sql = @"if exists (select * from   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{0}', 'column', default) where objname = '{1}') EXEC sp_updateextendedproperty   'MS_Description','{2}','user',dbo,'table','{0}','column',{1} else EXEC sp_addextendedproperty @name=N'MS_Description' , @value=N'{2}' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'{0}', @level2type=N'COLUMN', @level2name=N'{1}' ";
                upsert_sql = string.Format(upsert_sql, tableName, columnName, comment);
                Db.ExecSql(upsert_sql);
                
                List<ColumnInfo> lstColInfo = TableColumnInfoDict[tableName];

                NameValueCollection nvcColDesc = new NameValueCollection();
                lstColInfo.ForEach(t =>
                {
                    if (t.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase))
                    {
                        t.DeText = comment;
                    }
                    nvcColDesc.Add(t.ColumnName, t.DeText);
                });

                TableColumnInfoDict.Remove(tableName);
                TableColumnInfoDict.Add(tableName, lstColInfo);

                TableColumnComments.Remove(tableName);
                TableColumnComments.Add(tableName, nvcColDesc);
                
                ColumnInfo colInfo = DictColumnInfo[strKey];
                colInfo.DeText = comment;
                DictColumnInfo[strKey] = colInfo;

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public bool DropTable(string tableName)
        {
            if (!TableNames.Contains(tableName, StringComparer.OrdinalIgnoreCase))
            {
                throw new ArgumentException("该表不存在！" + tableName, "tableName");
            }

            string drop_sql = string.Empty;
            try
            {
               
                drop_sql = "drop table " + tableName;
                Db.ExecSql(drop_sql);
                
                this.TableComments.Remove(tableName);

                this.TableNames = TableComments.AllKeys.ToList();

                this.TableInfoDict.Remove(tableName);
                this.TableColumnInfoDict.Remove(tableName);
                this.TableColumnComments.Remove(tableName);

                var lstColName = TableColumnNameDict[tableName];

                foreach (var colName in lstColName)
                {
                    var strKey = (tableName + "@" + colName).ToLower();
                    this.DictColumnInfo.Remove(strKey);
                }

                this.TableColumnNameDict.Remove(tableName);

            }
            catch (Exception ex)
            {

                return false;
            }
            return true;
        }


        public bool DropColumn(string tableName, string columnName)
        {
            if (!TableNames.Contains(tableName, StringComparer.OrdinalIgnoreCase))
            {
                throw new ArgumentException("该表不存在！" + tableName, "tableName");
            }

            var strKey = (tableName + "@" + columnName).ToLower();

            if (!DictColumnInfo.ContainsKey(strKey))
            {
                throw new ArgumentException(tableName + "表不存在" + columnName + "列！", "columnName");
            }

            try
            {
                string drop_sql = "alter table {0} drop column {1}";
                drop_sql = string.Format(drop_sql, tableName, columnName);
                Db.ExecSql(drop_sql);
                
                this.DictColumnInfo.Remove(strKey);
                
                var nvc = TableColumnComments[tableName];
                nvc.Remove(columnName);
                TableColumnNameDict[tableName] = nvc.AllKeys.ToList();

                var lstColInfo = TableColumnInfoDict[tableName];
                ColumnInfo curColInfo = null;
                lstColInfo.ForEach(t =>
                {
                    if (t.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase))
                    {
                        curColInfo = t;

                        //tabInfo 对应的 主键类型和主键列 也需要 跟着修改。
                        if (curColInfo.IsPK)
                        {
                            var tabInfo = TableInfoDict[tableName];
                            tabInfo.PriKeyType = PrimaryKeyType.UNKNOWN;
                            tabInfo.PriKeyColName = null;
                            TableInfoDict[tableName] = tabInfo;
                        }

                        return;
                    }
                });
                lstColInfo.Remove(curColInfo);
                TableColumnInfoDict[tableName] = lstColInfo;

               
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }


    }
}
